﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;

namespace Pku.BookStore.DAL
{
    /// <summary>
    /// 数据访问类
    /// </summary>
    public class CommentDAL : BaseDAL
    {
        string commandText = string.Empty;

        /// <summary>
        /// 增加数据
        /// </summary>
        /// <param name="entity"></param>
        public int Add(Model.CommentModel entity)
        {
            commandText = @"INSERT INTO Comment(BookID,UserID,Content,Level,Title,AddDate)
VALUES(@BookID,@UserID,@Content,@Level,@Title,getdate());
SET  @CommentID=scope_identity();
";
            var command = DbMain.GetSqlStringCommand(commandText);
            DbMain.AddInParameter(command, "CommentID", DbType.Int32, entity.CommentID);
            DbMain.AddInParameter(command, "BookID", DbType.Int32, entity.BookID);
            DbMain.AddInParameter(command, "UserID", DbType.Int32, entity.UserID);
            DbMain.AddInParameter(command, "Content", DbType.String, entity.Content);
            DbMain.AddInParameter(command, "Level", DbType.Int32, entity.Level);
            DbMain.AddInParameter(command, "Title", DbType.String, entity.Title);
            DbMain.AddOutParameter(command, "CommentID", DbType.Int32, 10);
            DbMain.ExecuteNonQuery(command);

            return (int)DbMain.GetParameterValue(command, "CommentID");

        }

        /// <summary>
        /// 修改数据
        /// </summary>
        /// <param name="entity"></param>
        public bool Modify(Model.CommentModel entity)
        {
            commandText = @"UPDATE Comment SET BookID=@BookID,UserID=@UserID,Content=@Content,Level=@Level,Title=@Title,AddDate=@AddDate WHERE CommentID=@CommentID";
            var command = DbMain.GetSqlStringCommand(commandText);
            DbMain.AddInParameter(command, "CommentID", DbType.Int32, entity.CommentID);
            DbMain.AddInParameter(command, "BookID", DbType.Int32, entity.BookID);
            DbMain.AddInParameter(command, "UserID", DbType.Int32, entity.UserID);
            DbMain.AddInParameter(command, "Content", DbType.String, entity.Content);
            DbMain.AddInParameter(command, "Level", DbType.Int32, entity.Level);
            DbMain.AddInParameter(command, "Title", DbType.String, entity.Title);
            DbMain.AddInParameter(command, "AddDate", DbType.DateTime, entity.AddDate);

            return (int)DbMain.ExecuteNonQuery(command) == 1;
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <returns></returns>
        public List<Model.CommentModel> Search(Model.CommentParam param)
        {
            var list = new List<Model.CommentModel>();
            DbCommand dbCommand = DbMain.GetStoredProcCommand("CommentPageSearch");
            dbCommand.CommandTimeout = 600;
            DbMain.AddInParameter(dbCommand, "Page", DbType.Int32, param.PageNo);
            DbMain.AddInParameter(dbCommand, "Rows", DbType.Int32, param.PageRow);
            DbMain.AddInParameter(dbCommand, "DateStart", DbType.String, param.DateStart.ToString("yyyy-MM-dd 00:00:00"));
            DbMain.AddInParameter(dbCommand, "DateEnd", DbType.String, param.DateEnd.ToString("yyyy-MM-dd 23:59:59"));
            DbMain.AddInParameter(dbCommand, "BookID", DbType.Int32, param.BookID);
            DbMain.AddInParameter(dbCommand, "UserID", DbType.Int32, param.UserID);
            using (var dr = DbMain.ExecuteReader(dbCommand))
            {
                while (dr.Read())
                {
                    var m = new Model.CommentModel();
                    m = ConvertReaderToEntity(dr);
                    m.BookName = dr["BookName"].ToString();
                    m.UserName = dr["UserName"].ToString();
                    list.Add(m);
                }
            }
            return list;
        }

        /// <summary>
        ///  获取实体
        /// </summary>
        /// <param name="commentID"></param>
        /// <returns></returns>
        public Model.CommentModel GetEntityById(int commentID)
        {
            commandText = @"SELECT 
	                            c.CommentID,
	                            c.BookID,
	                            c.UserID,
	                            c.Content,
	                            c.[Level],
	                            c.Title,
	                            c.AddDate,
	                            u.UserName,
	                            p.BookName 
                            FROM  
	                            Comment c(NOLOCK)
	                            INNER JOIN [User] u(NOLOCK) ON u.UserID=c.UserID
	                            INNER JOIN Product p(NOLOCK) ON p.BookID=c.BookID
                            WHERE 
	                            CommentID=@CommentID";
            var command = DbMain.GetSqlStringCommand(commandText);
            DbMain.AddInParameter(command, "CommentID", DbType.Int32, commentID);
            using (var dr = DbMain.ExecuteReader(command))
            {
                if (dr.Read())
                {
                    var m = new Model.CommentModel();
                    m = ConvertReaderToEntity(dr);
                    m.BookName = dr["BookName"].ToString();
                    m.UserName = dr["UserName"].ToString();
                    return m;
                }
            }
            return null;
        }

        /// <summary>
        /// 转换实体
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        private static Model.CommentModel ConvertReaderToEntity(IDataRecord dr)
        {
            return new Model.CommentModel
            {
                CommentID = (int)dr["CommentID"],
                BookID = (int)dr["BookID"],
                UserID = (int)dr["UserID"],
                Content = dr["Content"].ToString(),
                Level = (int)dr["Level"],
                Title = dr["Title"].ToString(),
                AddDate = Convert.ToDateTime(dr["AddDate"])
            };
        }


        /// <summary>
        /// 是否存在
        /// </summary>
        /// <param name="Str">关键字</param>
        /// <returns></returns>
        public bool Exist(int BookID, int UserID)
        {
            commandText = "SELECT COUNT(1) FROM Comment p(NOLOCK) WHERE p.BookID=@BookID AND p.UserID=@UserID";
            var command = DbMain.GetSqlStringCommand(commandText.ToString());
            DbMain.AddInParameter(command, "BookID", DbType.Int32, BookID);
            DbMain.AddInParameter(command, "UserID", DbType.Int32, UserID);
            return (int)DbMain.ExecuteScalar(command) > 0;
        }

        /// <summary>
        /// 获取总数
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public int GetCount(Model.PublisherParam param)
        {
            string commandText = "SELECT COUNT(1) FROM Comment o(NOLOCK) WHERE 1=1";
            var command = DbMain.GetSqlStringCommand(commandText);
            return (int)DbMain.ExecuteScalar(command);
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="CategoryID"></param>
        /// <returns></returns>
        public bool Delete(int CommentID)
        {
            commandText = "DELETE Comment WHERE CommentID=@CommentID";
            var command = DbMain.GetSqlStringCommand(commandText.ToString());
            DbMain.AddInParameter(command, "CommentID", DbType.Int32, CommentID);
            return (int)DbMain.ExecuteNonQuery(command) > 0;
        }
    }

}